package com.myteam.fugaocrm.sqldb.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;

import com.myteam.fugaocrm.modle.FgContractDetail;

public interface FgContractDetailMapper {

	@Select("select * from fg_contract_detail where is_delete = 0 and project_id = #{projectId}")
	public List<FgContractDetail> selectByProjectId(Integer projectId);
	
	@Select("update fg_contract_detail set detail_type=#{detailType}, is_Billing=#{isBilling}, is_Payment=#{isPayment}, billing_Type=#{billingType}, billing_rate=#{billingRate},billing_money=#{billingMoney}, payment_money=#{paymentMoney}, payment_rate=#{paymentRate}, billing_Date=#{billingDate}, payment_Date=#{paymentDate},modified_user=#{modifiedUser},modified_date=#{modifiedDate} where contract_detail_id = #{contractDetailId}")
	public void updateOne(FgContractDetail fgContractDetail);
	
	@Insert("INSERT into fg_contract_detail( contract_id, project_id, detail_type, is_billing, is_payment,  billing_type, billing_rate,billing_money, payment_rate, payment_money, billing_date, payment_date, created_user, modified_user, created_date, modified_date, is_delete) "
			+ "VALUES(#{contractId},#{projectId},#{detailType},#{isBilling},#{isPayment},#{billingType},#{billingRate},#{billingMoney},#{paymentRate}, #{paymentMoney}, #{billingDate},#{paymentDate},#{createdUser},#{modifiedUser},#{createdDate},#{modifiedDate},#{isDelete});")
	public void insertOne(FgContractDetail fgContractDetail);
	
	//查询全部合同明细待开票
	@Select(
		"SELECT " +
			"contract.*, " +
			"project.project_name " +
		"FROM fg_contract_detail contract " +
		"LEFT JOIN fg_project project ON project.project_id = contract.project_id " +
		"WHERE contract.is_delete = 0 AND contract.is_billing = 'N' AND billing_date IS NOT NULL AND to_days(billing_date) < to_days(now())"
	)
	public List<FgContractDetail> selectAllWaitBill();
	
	//查询全部合同明细待支付
	@Select(
		"SELECT " +
			"contract.*, " +
			"project.project_name " +
		"FROM fg_contract_detail contract " +
		"LEFT JOIN fg_project project ON project.project_id = contract.project_id " +
		"WHERE contract.is_delete = 0 AND contract.is_payment = 'N' AND payment_date IS NOT NULL AND to_days(payment_date) < to_days(now())"
	)
	public List<FgContractDetail> selectAllWaitPay();
	
	// 查询未回款报表数据（根据项目ID）
	@Select(
			"SELECT  " +
			"	SUM(c.billing_rate) billingRate, " +
			"	SUM(c.billing_money) billingMoney, " +
			"	SUM(c.payment_rate) paymentRate, " +
			"	SUM(c.payment_money) paymentMoney " +
			"FROM  " +
			"	fg_contract_detail c " +
			"WHERE  " +
			"	c.project_id = #{projectId} " +
			"	AND c.is_delete = 0 " +
			"	AND (c.is_payment = 'Y' OR c.is_billing = 'N') "
		)
		public FgContractDetail selectNotPayment(Integer projectId);
}
